package com.ctshk.rpc.order.custom.mapper;

import java.util.List;

import com.ctshk.rpc.order.custom.req.schedule.TripScheduleInfoReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ctshk.rpc.order.custom.dto.tourist.CustomOrderTouristRoomDTO;
import com.ctshk.rpc.order.custom.dto.tourist.CustomOrderTouristRoomGroupDTO;
import com.ctshk.rpc.order.custom.entity.CustomizedOrderTouristRoom;
import com.ctshk.rpc.order.custom.req.tourist.CustomOrderTouristRoomReq;

/**
 * <p>
 * 游客分房 Mapper 接口
 * </p>
 *
 * @author 叶冠峰
 * @since 2021-02-22
 */
public interface CustomizedOrderTouristRoomMapper extends BaseMapper<CustomizedOrderTouristRoom> {
	
	/**
     * 旅客分组列表
     */
    @Select("<script>"+"SELECT \n" +
            "gp.id groupId,gp.group_name groupName,t.lock_status lockStatus,t.noShow noShow, t.en_fullname enFullName,t.cn_fullname cnFullName,\n" +
            "t.people_type peopleType, t.tourist_type_name touristTypeName, t.pay_status payStatus, t.birthday birthday, t.gender gender, \n" +
            "t.nation nation, t.mobile mobile, t.email email, od.order_number orderNumber, od.order_source orderSource, t.create_id createId,\n" +
            "t.emergency_contact_name emergencyContactName, t.remark remark\n" +
            "FROM t_customized_order_tourist_info t LEFT JOIN t_customized_order od ON t.order_id = od.id LEFT JOIN t_customized_order_tourist_room rm ON \n" +
            "t.room_id = rm.id LEFT JOIN t_customized_order_tourist_group gp ON t.group_id = gp.id WHERE t.is_deleted = 0 AND od.is_deleted = 0 AND \n" +
            "t.schedule_id = #{roomReq.scheduleId}\n" +
            "<if test=\"roomReq.orderNumber != null and roomReq.orderNumber != ''\">and od.order_number LIKE CONCAT('%',#{roomReq.orderNumber},'%')</if>\n" +
            "<if test=\"roomReq.name != null and roomReq.name != ''\">and (t.cn_fullname LIKE CONCAT('%',#{roomReq.name},'%') or t.en_fullname LIKE CONCAT('%',#{roomReq.name},'%'))</if>\n" +
            "<if test=\"roomReq.touristTypeName != null and roomReq.touristTypeName != ''\">and t.tourist_type_name LIKE CONCAT('%',#{roomReq.touristTypeName},'%')</if>\n" +
            "<if test=\"roomReq.gender != null\">and t.gender = #{roomReq.gender}</if>\n" +
            "<if test=\"roomReq.groupName != null and roomReq.groupName != -1\">and gp.group_name = #{roomReq.groupName}</if>\n" +
            "<if test=\"roomReq.groupId != null\">and gp.id = #{roomReq.groupId}</if>\n" +
            "<if test=\"roomReq.lockStatus != null\">and t.lock_status = #{roomReq.lockStatus}</if>\n" +
            "<if test=\"roomReq.noShow != null\">and t.noShow = #{roomReq.noShow}</if>\n" +
            "<if test=\"roomReq.payStatus != null\">and t.pay_status = #{roomReq.payStatus}</if>\n" +
            "<if test=\"roomReq.orderSource != null\">and od.order_source = #{roomReq.orderSource}</if>\n" +
            "ORDER BY t.gmt_create DESC"+"</script>")
    IPage<CustomOrderTouristRoomGroupDTO> queryList(Page<CustomOrderTouristRoomGroupDTO> iPage, @Param("roomReq")CustomOrderTouristRoomReq roomReq);

	/**
     * 旅客所有分房
     */
    @Select("<script>"+"SELECT \n" +
            "t.id id, rm.room_number roomNumber,t.lock_status lockStatus,t.noShow noShow, t.en_fullname enFullName,t.cn_fullname cnFullName,\n" +
            "t.people_type peopleType, t.tourist_type_name touristTypeName, t.pay_status payStatus, t.birthday birthday, t.gender gender, \n" +
            "t.nation nation, t.mobile mobile, t.email email, od.order_number orderNumber, od.order_source orderSource, t.create_id createId,\n" +
            "t.emergency_contact_name emergencyContactName, t.remark remark\n" +
            "FROM t_customized_order_tourist_info t LEFT JOIN t_customized_order od ON t.order_id = od.id LEFT JOIN t_customized_order_tourist_room rm ON \n" +
            "t.room_id = rm.id LEFT JOIN t_customized_order_tourist_group gp ON t.group_id = gp.id WHERE t.is_deleted = 0 AND od.is_deleted = 0 AND \n" +
            "t.schedule_id = #{roomReq.scheduleId}\n" +
            "ORDER BY t.gmt_create DESC"+"</script>")
    List<CustomOrderTouristRoomDTO> roomList(@Param("roomReq")CustomOrderTouristRoomReq roomReq);
    
    @Select("SELECT SUM(room_number) FROM t_customized_order_tourist_room WHERE schedule_id = #{schedule_id} AND is_deleted = 0")
    Long queryMaxRoom(@Param("schedule_id")Long scheduleId);

    /**
     * 根据团期获取旅客分组列表
     * @param scheduleId
     * @return
     */
    @Select("SELECT \n" +
            "gp.id groupId,gp.group_name groupName,t.lock_status lockStatus,t.noShow noShow, t.en_fullname enFullName,t.cn_fullname cnFullName,\n" +
            "t.people_type peopleType, t.tourist_type_name touristTypeName, t.pay_status payStatus, t.birthday birthday, t.gender gender, \n" +
            "t.nation nation, t.mobile mobile, t.email email, od.order_number orderNumber, od.order_source orderSource, t.create_id createId,\n" +
            "t.emergency_contact_name emergencyContactName,t.emergency_mobile_area_code,t.emergency_mobile, t.remark remark\n" +
            "FROM t_customized_order_tourist_info t LEFT JOIN t_customized_order od ON t.order_id = od.id LEFT JOIN t_customized_order_tourist_room rm ON \n" +
            "t.room_id = rm.id LEFT JOIN t_customized_order_tourist_group gp ON t.group_id = gp.id WHERE t.is_deleted = 0 AND od.is_deleted = 0 AND \n" +
            "t.schedule_id = #{id}\n" +
            "ORDER BY t.gmt_create DESC")
    List<CustomOrderTouristRoomGroupDTO> queryBySchedule(@Param("id")Long scheduleId);

    /**
     * 根据团期获取旅客分房列表
     * @param roomReq
     * @return
     */
    @Select("SELECT \n" +
            "t.id id, rm.room_number roomNumber,t.lock_status lockStatus,t.noShow noShow, t.en_fullname enFullName,t.cn_fullname cnFullName,\n" +
            "t.people_type peopleType, t.tourist_type_name touristTypeName, t.pay_status payStatus, t.birthday birthday, t.gender gender, \n" +
            "t.nation nation, t.mobile mobile, t.email email, od.order_number orderNumber, od.order_source orderSource, t.create_id createId,\n" +
            "t.emergency_contact_name emergencyContactName,t.emergency_mobile_area_code,t.emergency_mobile, t.remark remark\n" +
            "FROM t_customized_order_tourist_info t LEFT JOIN t_customized_order od ON t.order_id = od.id LEFT JOIN t_customized_order_tourist_room rm ON \n" +
            "t.room_id = rm.id LEFT JOIN t_customized_order_tourist_group gp ON t.group_id = gp.id WHERE t.is_deleted = 0 AND od.is_deleted = 0 AND \n" +
            "t.schedule_id = #{roomReq.scheduleId}\n" +
            "ORDER BY t.gmt_create DESC")
    List<CustomOrderTouristRoomDTO> queryRoomBySchedule(@Param("roomReq") TripScheduleInfoReq roomReq);
}
